﻿
import MySQLdb

class db_cnbeta:
	def __init__(self, db_name = 'db_cnbeta'):
		self.connect_db(db_name)
		self.db_name = db_name

	def connect_db(self, db_name):
		if db_name:
			conn = MySQLdb.connect(host = 'localhost', user = 'root', passwd = 'psd-9898w', db = db_name)
		else:
			conn = MySQLdb.connect(host = 'localhost', user = 'root', passwd = 'psd-9898w')
		self.conn = conn
		cursor = conn.cursor()
		cursor.execute("SET NAMES utf8")
		cursor.execute("SET CHARACTER_SET_CLIENT=utf8")
		cursor.execute("SET CHARACTER_SET_RESULTS=utf8")
		cursor.execute('SET AUTOCOMMIT = 1')
		if db_name:
			cursor.execute('USE %s' % db_name)
		self.db_name = db_name
		
	def execute_sql(self, sql, param = None):
		print 'TRACE: %s' % sql
		if not self.conn:
			print "error, cannot connect to db"
		#	return#不返回，直接让它抛异常
		if not self.conn.cursor:
			print "can not get cursor"
		cursor = self.conn.cursor()
		dir( cursor )
		if param:
			cursor.execute(sql, param)
		else:
			cursor.execute(sql)
		return cursor
		
	def execute_scaler(self, sql, param = None):
		cursor = self.execute_sql(sql, param)
		row = cursor.fetchone()
		return row[0]

	def executemany(self, sql, param):
		if not self.conn:
			print "error, cannot connect to db"
		if not self.conn.cursor:
			print "can not get cursor"
		cursor = self.conn.cursor()
		cursor.executemany(sql, param)
		
	def create_db(self, db_name):
		self.db_name = db_name
		sql = '''create database if not exists %s;''' % self.db_name
		self.execute_sql(sql)
		self.execute_sql('use %s' % self.db_name)
		# 直接通过shell脚本创建

	def add_author(self, pick_url, name):
		sql = '''insert into tb_author(pick_url, name) value(%s, %s);'''
		self.execute_sql(sql, (pick_url, name))

	def is_author_exists(self, pick_url):
		sql = '''select count(0) from tb_author where pick_url=%s;'''
		return self.execute_scaler(sql, (pick_url, )) > 0

	def add_tag(self, pick_url, name):
		sql = '''insert into tb_tag(pick_url, name) value(%s, %s);'''
		self.execute_sql(sql, (pick_url, name))

	def is_tag_exists(self, pick_url):
		sql = '''select count(0) from tb_tag where pick_url=%s;'''
		return self.execute_scaler(sql, (pick_url, )) > 0

	def add_category(self, pick_url, name):
		sql = '''insert into tb_category(pick_url, name) value(%s, %s);'''
		self.execute_sql(sql, (pick_url, name))

	def is_category_exists(self, pick_url):
		sql = '''select count(0) from tb_category where pick_url=%s;'''
		return self.execute_scaler(sql, (pick_url, )) > 0

	def add_source(self, pick_url, name):
		sql = '''insert into tb_source(pick_url, name) value(%s, %s);'''
		self.execute_sql(sql, (pick_url, name))

	def is_source_exists(self, pick_url):
		sql = '''select count(0) from tb_source where pick_url=%s;'''
		return self.execute_scaler(sql, (pick_url, )) > 0

	def add_topics(self, pick_url, img_url, name):
		sql = '''insert into tb_topics(pick_url, img_url, name) value(%s, %s);'''
		self.execute_sql(sql, (pick_url, name))

	def is_source_exists(self, pick_url):
		sql = '''select count(0) from tb_source where pick_url=%s;'''
		return self.execute_scaler(sql, (pick_url, )) > 0

	def post_click(self, postid, userip):
		sql = '''insert into tb_post_click(postid, userip) value(%s, %s);'''
		self.execute_sql(sql, (postid, userip))

	def is_post_exists(self, pick_url):
		sql = '''select count(0) from tb_post where pick_url=%s;''' #% pick_url
		return self.execute_scaler(sql, (pick_url, )) > 0

	def get_post_id(self, pick_url):
		sql = '''select id from tb_post where pick_url=%s;'''
		return self.execute_scaler(sql, (pick_url, )) > 0

	def get_tag_id(self, pick_url):
		sql = '''select id from tb_tag where pick_url=%s;'''
		return self.execute_scaler(sql, (pick_url, )) > 0

	def get_author_id(self, pick_url):
		sql = '''select id from tb_author where pick_url=%s;'''
		return self.execute_scaler(sql, (pick_url, )) > 0

	def get_source_id(self, pick_url):
		sql = '''select id from tb_source where pick_url=%s;'''
		return self.execute_scaler(sql, (pick_url, )) > 0

	def get_topics_id(self, pick_url):
		sql = '''select id from tb_topics where pick_url=%s;'''
		return self.execute_scaler(sql, (pick_url, )) > 0

'''
CREATE TABLE IF NOT EXISTS tb_post(
	ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	post_author bigint(20) unsigned NOT NULL DEFAULT 0,
	post_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00', /*原始发布时间*/
	post_source bigint(20) unsigned NOT NULL DEFAULT 0,/*来源*/
	post_topics bigint(20) unsigned NOT NULL DEFAULT 0,/*主题分类*/
	post_title text NOT NULL,
	post_abstract text NOT NULL,
	post_content text NOT NULL,
	post_status bigint(20) unsigned NOT NULL DEFAULT 0,/*是否可见*/
	post_ping bigint(20) unsigned NOT NULL DEFAULT 0,
	post_comment bigint(20) NOT NULL DEFAULT 0,
	post_mark bigint(20) DEFAULT 0,/*评分*//*抓取时间*/
	pick_date datetime NOT NULL DEFAULT NOW(),
	pick_url varchar(1024) NOT NULL DEFAULT '', /*源网址*/
/*	post_tags varchar(20) NOT NULL DEFAULT '',*/ /*以逗号分割*/
/*	post_category varchar(10) NOT NULL DEFAULT '',*/ /*以逗号分割*/
	primary key(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;'''
	def add_post(self, pick_url, title, post_date_get, , datepublished, , read_url, description):
		sql = '''insert into tb_post(pick_url, title, img_url, isbn, datepublished, pages, language, file_size, file_format, down_url, buy_url, read_url, description) value(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);'''
		self.execute_sql(sql, (pick_url, title, title1, img_url, isbn, str(datepublished), str(pages), language, file_size, file_format, down_url, buy_url, read_url, description))
		post_id = self.execute_scaler('select last_insert_id();')
		return post_id

	def add_post_author(self, postid_authorids): #deprecrate
		sql = '''insert into tb_post_author(postid, authorid) values (%s, %s);'''
		self.executemany(sql, list(postid_authorids))

	def add_post_category(self, postid_publisherids):
		sql = '''insert into tb_post_category(postid, publisherid) values (%s, %s);'''
		self.executemany(sql, list(postid_publisherids))

	def add_post_tag(self, postid_tagids):
		sql = '''insert into tb_post_tag(postid, tagid) values (%s, %s);'''
		self.executemany(sql, list(postid_tagids))
		
	def add_comment(self,  postid, userid, userip, content):
		sql = 'insert into tb_comment(post_id, user_id, user_ip, content) values(%d, %d, %s, %s);'
		self.execute_sql(sql, (postid, userid, userip, content))
		
	def add_mark(self, postid, userid, userip, mark):
		sql = 'insert into tb_mark(post_id, user_id, user_ip, mark) values(%d, %d, %s, %s);'
		self.execute_sql(sql, (postid, userid, userip, mark))
		sql = 'update tb_post set post_mark+=%d where ID=%d;'
		self.execute_sql(sql, (postid, mark))

if __name__ == '__main__':
	print '你好, world'
	cnbeta = db_cnbeta(None)
	cnbeta.create_db('cnbeta')
